package com.baoxin.utils.dao;

import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

/**
 * @Author: Rose
 * @Date: 2021/3/24 13:14
 * @version: 1.0.0
 */
@Transactional
@Component
public class CommonQuerySQL {

    @PersistenceContext
    private  EntityManager em;

    //自定义动态报表原生sql查询
    //只要isSelect不为空，那么就执行条件查询：前端自行根据返回值判断是否已经选题(大于0就是选了题的)
    public Page<StudentToAdminVO> findAllStudentToAdminVOPage(Pageable pageable, String majorId, Boolean isSelect, Boolean isSelectSuccess) {
        //设置条件
        StringBuffer where = new StringBuffer(" where 1=1 ");
        StringBuffer having = new StringBuffer(" having 1=1");
        if(!StringUtils.isEmpty(majorId)){
            where.append(" and s.major_id=:majorId ");
        }
        if(isSelect!=null){
            //是否选题了，只需要看查出的这个数是否大于零即可
            if(isSelect) {
                having.append(" and count(se.id)>0 ");
            }else {
                having.append(" and count(se.id)=0 ");
            }
        }
        if(isSelectSuccess != null){
            if(isSelectSuccess) {
                having.append(" and max(se.is_select)>0");
            }else {
                having.append(" and (max(se.is_select) is null or max(se.is_select)<=0)");
            }
        }
        //主体sql
        String sql = "select s.id, s.username, s.nickname, s.sclass, m.name majorName, count(se.id) as choose, max(se.is_select) as selectSuccess from student s"
                + " left join selection se on s.id=se.student_id "
                + " left join major m on m.id=s.major_id "
                + where
                + " group by s.id"
                + having;
        String countSql = null;
        //计算总记录数sql
        if(isSelect!=null){
            countSql = "select count(*) from student s "
                    + where
                    + " and s.id in(select ss.id FROM student ss left join selection se on se.student_id=ss.id GROUP BY ss.id "
                    + having
                    + " )";
        }else{
            countSql = "select count(*) from student s " + where;
        }
        //创建原生查询
        Query query = em.createNativeQuery(sql);
        Query countQuery = em.createNativeQuery(countSql);
        if(!StringUtils.isEmpty(majorId)){
            query.setParameter("majorId", majorId);
            countQuery.setParameter("majorId", majorId);
        }
        int total = Integer.valueOf(countQuery.getSingleResult().toString());
//		pageable.getPageNumber()==0 ? pageable.getOffset() : pageable.getOffset()-5
        if(pageable!=null){
            query.setFirstResult(pageable.getOffset());
            query.setMaxResults(pageable.getPageSize());
        }
        //对象映射
        query.unwrap(SQLQuery.class)
                .addScalar("id", StandardBasicTypes.STRING)
                .addScalar("username", StandardBasicTypes.STRING)
                .addScalar("nickname", StandardBasicTypes.STRING)
                .addScalar("sclass", StandardBasicTypes.STRING)
                .addScalar("majorName", StandardBasicTypes.STRING)
                .addScalar("choose", StandardBasicTypes.INTEGER)
                .addScalar("selectSuccess", StandardBasicTypes.INTEGER)
                .setResultTransformer(Transformers.aliasToBean(StudentToAdminVO.class));

        return new PageImpl<StudentToAdminVO>(query.getResultList(), pageable, total);
    }
    //版权声明：本文为CSDN博主「梅仁」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
    //原文链接：https://blog.csdn.net/Phapha1996/article/details/83614975
}
